﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using BartonExtension;

namespace SqliteWrapper.Query
{
    public class QueryBuilder
    {
        private List<QueryJoin> _joins;
        private List<SQLiteParameter> _parameters;
        private string _tbName;
        private string[] _willQueryAttributes;

        public bool QueryTimeRange { get; set; }
        public DateTime? StartTime { get; set; }
        public DateTime? EndTime { get; set; }

        public bool QueryLimitation { get; set; }
        public bool QueryNoCondition { get; set; }
        public int? StartIndex { get; set; }
        public int? Length { get; set; }
        public QueryOrder OrderBy { get; set; }

        public QueryBuilder(string tbName,params string[] willQueryAttributes)
        {
            _tbName = tbName;
            _willQueryAttributes = willQueryAttributes;
            _parameters = new List<SQLiteParameter>();
            _joins = new List<QueryJoin>();
            QueryNoCondition = false;
            QueryLimitation = false;
            QueryTimeRange = false;
        }

        public void addJoin(QueryJoin join)
        {
            _joins.Add(join);
        }

        public string BuildSelect()
        {
            if (_parameters == null)
                QueryNoCondition = true;
            if(StartIndex != null && Length != null)
                QueryLimitation = true;
            if(StartTime != null && EndTime != null)
                QueryTimeRange = true;

            string query = "";
            
            query += "select ";
            foreach (string attr in _willQueryAttributes)
            {
                query += "{0},".FormatWith(attr);
            }
            query = query.TrimEnd(',');
            query += " from {0} ".FormatWith(_tbName);
            
            foreach (QueryJoin join in _joins)
            {
                query += join.BuildSql();
            }

            query += "where 1 = 1 ";
            if (!QueryNoCondition)
            {
                foreach(SQLiteParameter param in _parameters)
                {
                    switch (param.DbType)
                    {
                        case DbType.Int64:
                        case DbType.Int32:
                        case DbType.Int16:
                        case DbType.UInt16:
                        case DbType.UInt32:
                        case DbType.UInt64:
                            query += "and {0} = {1} ".FormatWith(param.ParameterName, param.Value);
                            break;
                        case DbType.String:
                            query += "{0} like {1}% ".FormatWith(param.ParameterName, param.Value);
                            break;
                        default:
                            break;
                    }
                }
            }
            if(QueryTimeRange)
            {
                query += "and CreateTimeTag > {0} and CreateTimeTag < {1} ".FormatWith(StartTime.Value.Date.ToShortDateString(),EndTime.Value.Date.ToShortDateString());
            }

            if (OrderBy != null)
            {
                query += "{0} ".FormatWith(OrderBy.GetOrderBy());
            }

            if (QueryLimitation)
            {
                query += "Limit {0},{1} ".FormatWith(StartIndex, Length);
            }

            return query;
        }

        public List<SQLiteParameter> Parameters
        {
            get{return _parameters;}
            set{_parameters = value;}
        }

        public DataTable GetResult(SQLiteDataReader dr)
        {
            DataTable dt = new DataTable(_tbName);
            if (dr.HasRows)
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    dt.Columns.Add(dr.GetName(i),dr.GetFieldType(i));
                }
                while (dr.Read())
                {
                    DataRow row = dt.NewRow();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        row[dt.Columns[i]] = dr[i];
                    }
                    dt.Rows.Add(row);
                }
            }
            return dt;
        }
    }
}
